﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

using Dapper;

using KSOAdmin.Core.EFDbContext;
using KSOAdmin.Core.Expressions;

using Microsoft.VisualBasic.FileIO;

namespace KSOAdmin.Core.DapperDbContext
{
    /// <summary>
    /// 增删改查
    /// </summary>
    public static class DapperHelp
    {
        // 获取开启数据库的连接
        private static IDbConnection Db
        {
            get
            {
                //创建单一实例
                DapperContext.GetInstance();
                return DapperContext.OpenCurrentDbConnection();
            }
        }

        #region 查询
        /// <summary>
        /// 查出一条记录的实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static T QueryFirstOrDefault<T>(string sql, object param = null)
        {
            return Db.QueryFirstOrDefault<T>(sql, param);
        }

        public static Task<T> QueryFirstOrDefaultAsync<T>(string sql, object param = null)
        {
            return Db.QueryFirstOrDefaultAsync<T>(sql, param);
        }
        /// <summary>
        /// 查出多条记录的实体泛型集合
        /// </summary>
        /// <typeparam name="T">泛型T</typeparam>
        /// <returns></returns>
        public static IEnumerable<T> Query<T>(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            return Db.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType);
        }

        public static Task<IEnumerable<T>> QueryAsync<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return Db.QueryAsync<T>(sql, param, transaction, commandTimeout, commandType);
        }
        /// <summary>
        /// 同时查询多张表数据（高级查询）
        /// "select *from K_City;select *from K_Area";
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlMapper.GridReader QueryMultiple(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return Db.QueryMultiple(sql, param, transaction, commandTimeout, commandType);
        }
        public static Task<SqlMapper.GridReader> QueryMultipleAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return Db.QueryMultipleAsync(sql, param, transaction, commandTimeout, commandType);
        }
        #endregion

        #region 增 删 改
        /// <summary>
        /// 增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static int Execute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return Db.Execute(sql, param, transaction, commandTimeout, commandType);
        }

        public static Task<int> ExecuteAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return Db.ExecuteAsync(sql, param, transaction, commandTimeout, commandType);
        }

        public static T ExecuteScalar<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return Db.ExecuteScalar<T>(sql, param, transaction, commandTimeout, commandType);
        }

        public static Task<T> ExecuteScalarAsync<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return Db.ExecuteScalarAsync<T>(sql, param, transaction, commandTimeout, commandType);
        }
        #endregion
        #region 根据实体类进行操作
        /// <summary>
        /// 实体类批量增加
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entities"></param>
        /// <param name="addFileds"></param>
        /// <param name="beginTransaction"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static async Task<int> AddRange<T>(IEnumerable<T> entities, Expression<Func<T, object>> addFileds = null, IDbTransaction transaction = null  )
        {
            Type entityType = typeof(T);
            var key = entityType.GetKeyProperty();
            if (key == null)
            {
                throw new Exception("实体必须包括主键才能批量更新");
            }
            string[] columns;

            //指定插入的字段
            if (addFileds != null)
            {
                columns = addFileds.GetExpressionToArray();
            }
            else
            {
                var properties = entityType.GetGenericProperties();
                if (key.PropertyType != typeof(Guid))
                {
                    properties = properties.Where(x => x.Name != key.Name).ToArray();
                }
                columns = properties.Select(x => x.Name).ToArray();
            }
            string sql = null;

            //mysql批量写入待优化
            sql = $"insert into {entityType.Name}({string.Join(",", columns)})" +
             $"values(@{string.Join(",@", columns)});";
            sql = entities.GetEntitySql(key.PropertyType == typeof(Guid), sql, null, addFileds, null);
           //int row= await ExecuteAsync(sql, entities, transaction);
            return await ExecuteAsync(sql, entities, transaction);
        }

        /// <summary>
        ///(根据主键批量更新实体) 批量删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entities">实体必须带主键</param>
        /// <param name="updateFileds">批定更新字段</param>
        /// <param name="beginTransaction"></param>
        /// <returns></returns>
        public static async Task<int> DeleteRange<T>(int[] entities , IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            Type entityType = typeof(T);
            var key = entityType.GetKeyProperty();
            if (key == null)
            {
                throw new Exception("实体必须包括主键才能批量更新");
            }
            string sqltext = $@" DELETE FROM  { entityType.Name }  WHERE {entityType.GetKeyName()} IN ({string.Join(",", entities) }) ;";
            return await Db.ExecuteAsync(sqltext, null, transaction, commandTimeout, commandType);
        }
        public static string GetEntitySql<T>(this IEnumerable<T> entityList,
                bool containsKey = false,
                string sql = null,
                Expression<Func<T, object>> ignoreFileds = null,
                Expression<Func<T, object>> fixedColumns = null,
                FieldType? fieldType = null
                )
        {

            if (entityList == null || entityList.Count() == 0) return "";
            PropertyInfo[] propertyInfo = typeof(T).GetProperties().ToArray();
            if (propertyInfo.Count() == 0)
            {
                propertyInfo = entityList.ToArray()[0].GetType().GetGenericProperties().ToArray();
            }
            propertyInfo = propertyInfo.GetGenericProperties().ToArray();

            string[] arr = null;
            if (fixedColumns != null)
            {
                arr = fixedColumns.GetExpressionToArray();
                PropertyInfo keyProperty = typeof(T).GetKeyProperty();
                propertyInfo = propertyInfo.Where(x => (containsKey && x.Name == keyProperty.Name) || arr.Contains(x.Name)).ToArray();
            }
            if (ignoreFileds != null)
            {
                arr = ignoreFileds.GetExpressionToArray();
                propertyInfo = propertyInfo.Where(x => !arr.Contains(x.Name)).ToArray();
            }

            Dictionary<string, string> dictProperties = propertyInfo.GetColumType(containsKey);
            if (fieldType != null)
            {
                string realType = fieldType.ToString();
                if ((int)fieldType == 0 || (int)fieldType == 1)
                {
                    realType += "(max)";
                }
                dictProperties = new Dictionary<string, string> { { dictProperties.Select(x => x.Key).ToList()[0], realType } };
            }
            if (dictProperties.Keys.Count * entityList.Count() > 50 * 3000)
            {
                throw new Exception("写入数据太多,请分开写入。");
            }

            string cols = string.Join(",", dictProperties.Select(c => "[" + c.Key + "]" + " " + c.Value));
            StringBuilder declareTable = new StringBuilder();

            string tempTablbe = "#0" ;

            declareTable.Append("CREATE TABLE " + tempTablbe + " (" + cols + ")");
            declareTable.Append("\r\n");

            //参数总数量
            int parCount = (dictProperties.Count) * (entityList.Count());
            int takeCount = 0;
            int maxParsCount = 2050;
            if (parCount > maxParsCount)
            {
                //如果参数总数量超过2100，设置每次分批循环写入表的大小
                takeCount = maxParsCount / dictProperties.Count;
            }

            int count = 0;
            StringBuilder stringLeft = new StringBuilder();
            StringBuilder stringCenter = new StringBuilder();
            StringBuilder stringRight = new StringBuilder();

            int index = 0;
            foreach (T entity in entityList)
            {
                //每1000行需要分批写入(数据库限制每批至多写入1000行数据)
                if (index == 0 || index >= 1000 || takeCount - index == 0)
                {
                    if (stringLeft.Length > 0)
                    {
                        declareTable.AppendLine(
                            stringLeft.Remove(stringLeft.Length - 2, 2).Append("',").ToString() +
                            stringCenter.Remove(stringCenter.Length - 1, 1).Append("',").ToString() +
                            stringRight.Remove(stringRight.Length - 1, 1).ToString());

                        stringLeft.Clear(); stringCenter.Clear(); stringRight.Clear();
                    }

                    stringLeft.AppendLine("exec sp_executesql N'SET NOCOUNT ON;");
                    stringCenter.Append("N'");

                    index = 0; count = 0;
                }
                stringLeft.Append(index == 0 ? "; INSERT INTO  " + tempTablbe + "  values (" : " ");
                index++;
                foreach (PropertyInfo property in propertyInfo)
                {
                    if (!containsKey && property.IsKey()) { continue; }
                    string par = "@v" + count;
                    stringLeft.Append(par + ",");
                    stringCenter.Append(par + " " + dictProperties[property.Name] + ",");
                    object val = property.GetValue(entity);
                    if (val == null)
                    {
                        stringRight.Append(par + "=NUll,");
                    }
                    else
                    {
                        stringRight.Append(par + "='" + val.ToString().Replace("'", "''''") + "',");
                    }
                    count++;
                }
                stringLeft.Remove(stringLeft.Length - 1, 1);
                stringLeft.Append("),(");
            }

            if (stringLeft.Length > 0)
            {
                declareTable.AppendLine(
                    stringLeft.Remove(stringLeft.Length - 2, 2).Append("',").ToString() +
                    stringCenter.Remove(stringCenter.Length - 1, 1).Append("',").ToString() +
                    stringRight.Remove(stringRight.Length - 1, 1).ToString());

                stringLeft.Clear(); stringCenter.Clear(); stringRight.Clear();
            }
            if (!string.IsNullOrEmpty(sql))
            {
                sql = sql.Replace("0", tempTablbe);
                declareTable.AppendLine(sql);
            }
            else
            {
                declareTable.AppendLine(" SELECT " + (string.Join(",", fixedColumns?.GetExpressionToArray() ?? new string[] { "*" })) + " FROM " + tempTablbe);
            }


            if (tempTablbe.Substring(0, 1) == "#")
            {
                declareTable.AppendLine("; drop table " + tempTablbe);
            }
            return declareTable.ToString();
        }
        #endregion


    }
}